import type { Prisma } from '@prisma/client'

export async function bootstrapDatabaseIfNeeded(prisma: Prisma.DefaultPrismaClient): Promise<void> {
  // 幂等初始化与增量迁移：无需预先检查是否存在
  // 不再提前返回：始终执行幂等的建表/索引语句与增量迁移（CREATE IF NOT EXISTS / ALTER IF MISSING）

  // 增量迁移：为 appointment_management 表增加缺失列/索引
  try {
    const rows: any[] = await prisma.$queryRawUnsafe(`PRAGMA table_info('appointment_management');`)
    const cols = Array.isArray(rows) ? rows.map((r: any) => String(r?.name || '')) : []
    if (!cols.includes('is_monitor')) {
      await prisma.$executeRawUnsafe(`ALTER TABLE appointment_management ADD COLUMN is_monitor INTEGER NOT NULL DEFAULT 0;`)
    }
    // 新增：user_agent 列（可空，运行期可回写或由占号生成）
    if (!cols.includes('user_agent')) {
      await prisma.$executeRawUnsafe(`ALTER TABLE appointment_management ADD COLUMN user_agent TEXT;`)
    }
    // 幂等创建索引
    await prisma.$executeRawUnsafe(`CREATE INDEX IF NOT EXISTS ix_appointment_mgmt_is_monitor ON appointment_management(is_monitor);`)
    // 兜底：把可能存在的 NULL 值统一归 0（某些环境历史迁移可能生成了可空列）
    try {
      await prisma.$executeRawUnsafe(`UPDATE appointment_management SET is_monitor=0 WHERE is_monitor IS NULL;`)
    }
    catch {}
  }
  catch {}

  const stmts: string[] = [
    // appointment_management
    `CREATE TABLE IF NOT EXISTS appointment_management (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      sequence_number TEXT NOT NULL UNIQUE,
      passport_number TEXT NOT NULL,
      license_number TEXT NOT NULL,
      full_name TEXT NOT NULL,
      email TEXT NOT NULL,
      phone_number TEXT,
      issuing_country TEXT NOT NULL DEFAULT 'CN',
      issuing_authority TEXT NOT NULL DEFAULT 'GD',
      service_type TEXT NOT NULL DEFAULT 'DI',
      preferred_dates TEXT,
      preferred_times TEXT,
      priority INTEGER NOT NULL DEFAULT 1,
      remarks TEXT,
      is_placeholder INTEGER NOT NULL DEFAULT 0,
      is_monitor INTEGER NOT NULL DEFAULT 0,
      appointment_status TEXT NOT NULL DEFAULT 'pending',
      reference_number TEXT UNIQUE,
      appointment_date TEXT,
      appointment_time TEXT,
      timeslot_id TEXT,
      office_code TEXT NOT NULL DEFAULT 'HKLO',
      user_agent TEXT,
      batch_id TEXT,
      job_id TEXT,
      booking_mode TEXT NOT NULL DEFAULT 'manual',
      error_message TEXT,
      raw_response TEXT,
      retry_count INTEGER NOT NULL DEFAULT 0,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      booked_at DATETIME
    );`,
    `CREATE UNIQUE INDEX IF NOT EXISTS ux_appointment_mgmt_user ON appointment_management(passport_number, license_number);`,
    `CREATE INDEX IF NOT EXISTS ix_appointment_mgmt_status ON appointment_management(appointment_status);`,
    `CREATE INDEX IF NOT EXISTS ix_appointment_mgmt_is_placeholder ON appointment_management(is_placeholder);`,
    `CREATE INDEX IF NOT EXISTS ix_appointment_mgmt_is_monitor ON appointment_management(is_monitor);`,
    `CREATE INDEX IF NOT EXISTS ix_appointment_mgmt_priority ON appointment_management(priority);`,
    `CREATE INDEX IF NOT EXISTS ix_appointment_mgmt_batch ON appointment_management(batch_id);`,
    `CREATE INDEX IF NOT EXISTS ix_appointment_mgmt_created ON appointment_management(created_at);`,

    // batch_booking
    `CREATE TABLE IF NOT EXISTS batch_booking (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT,
      total_count INTEGER NOT NULL DEFAULT 0,
      success_count INTEGER NOT NULL DEFAULT 0,
      failed_count INTEGER NOT NULL DEFAULT 0,
      pending_count INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'created',
      concurrent_limit INTEGER NOT NULL DEFAULT 1,
      retry_failed INTEGER NOT NULL DEFAULT 0,
      auto_release_placeholder INTEGER NOT NULL DEFAULT 0,
      booking_options TEXT,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      started_at DATETIME,
      completed_at DATETIME
    );`,

    // quick_booking_config
    `CREATE TABLE IF NOT EXISTS quick_booking_config (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT,
      is_default INTEGER NOT NULL DEFAULT 0,
      service_type TEXT NOT NULL DEFAULT 'DI',
      office_codes TEXT NOT NULL,
      time_ranges TEXT NOT NULL,
      date_range_days INTEGER NOT NULL DEFAULT 30,
      max_attempts INTEGER NOT NULL DEFAULT 3,
      enable_placeholder INTEGER NOT NULL DEFAULT 0,
      placeholder_ratio REAL NOT NULL DEFAULT 0.2,
      auto_release_hours INTEGER NOT NULL DEFAULT 24,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );`,

    // appointment_operation_log
    `CREATE TABLE IF NOT EXISTS appointment_operation_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      appointment_id INTEGER NOT NULL,
      operation_type TEXT NOT NULL,
      operation_data TEXT,
      result_status TEXT NOT NULL,
      result_message TEXT,
      operator TEXT NOT NULL DEFAULT 'system',
      timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    );`,
    `CREATE INDEX IF NOT EXISTS ix_aop_log_apt ON appointment_operation_log(appointment_id);`,
    `CREATE INDEX IF NOT EXISTS ix_aop_log_type ON appointment_operation_log(operation_type);`,
    `CREATE INDEX IF NOT EXISTS ix_aop_log_ts ON appointment_operation_log(timestamp);`,

    // availability_cache
    `CREATE TABLE IF NOT EXISTS availability_cache (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      cache_key TEXT NOT NULL UNIQUE,
      service_type TEXT NOT NULL,
      office_code TEXT NOT NULL,
      date TEXT NOT NULL,
      available_timeslots TEXT NOT NULL,
      total_slots INTEGER NOT NULL DEFAULT 0,
      available_slots INTEGER NOT NULL DEFAULT 0,
      last_updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      expires_at DATETIME NOT NULL,
      source TEXT NOT NULL DEFAULT 'api'
    );`,
    `CREATE INDEX IF NOT EXISTS ix_av_cache_key ON availability_cache(cache_key);`,
    `CREATE INDEX IF NOT EXISTS ix_av_cache_scoped ON availability_cache(service_type, office_code, date);`,
    `CREATE INDEX IF NOT EXISTS ix_av_cache_exp ON availability_cache(expires_at);`,

    // jobs
    `CREATE TABLE IF NOT EXISTS jobs (
      id TEXT PRIMARY KEY,
      type TEXT NOT NULL,
      mode TEXT,
      status TEXT NOT NULL DEFAULT 'pending',
      progress INTEGER NOT NULL DEFAULT 0,
      current_step TEXT,
      user_info TEXT,
      options TEXT,
      result TEXT,
      error_message TEXT,
      trace_id TEXT,
      created_at INTEGER NOT NULL DEFAULT 0,
      updated_at INTEGER NOT NULL DEFAULT 0,
      started_at INTEGER,
      completed_at INTEGER
    );`,
    `CREATE INDEX IF NOT EXISTS ix_jobs_status ON jobs(status);`,
    `CREATE INDEX IF NOT EXISTS ix_jobs_type ON jobs(type);`,
    `CREATE INDEX IF NOT EXISTS ix_jobs_created ON jobs(created_at);`,
  ]

  for (const sql of stmts) {
    try {
      await prisma.$executeRawUnsafe(sql)
    }
    catch {
      // continue best-effort; remaining statements may still succeed
    }
  }
}
